﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DigDes.DSchool.SUPS.DataAccess.Interface;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;



namespace DigDes.DSchool.SUPS.DataAccess.Database
{
    
    public class DislocRow
    {
        public string InventoryCarriageNumber { get; set; }
        public string WeightTons { get; set; }
        public string DestStation { get; set; }
        public string CargoName { get; set; }
        public string ConsigneeCode { get; set; }
        public string ParkType { get; set; }
        public string StartStation { get; set; }
        public string CarOperation { get; set; }
        public string Date { get; set; }
        public string Time { get; set; }
        public string OpStation { get; set; }
        public string DelRoad { get; set; }
        public string RecRoad { get; set; }
        public string TrainIndex { get; set; }
        public string TrainNumber { get; set; } 
    }
    public class CargoRows
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public string Code { get; set; }   
  
        public string Mnemocode { get; set; }
        public bool IsUsed { get; set; }
        public bool IsEmpty { get; set; }
        public object Data { get; set; }
    }
    public class ReportRow
    {
        public int TravelTimeMin { get; set; }
        public string CarNum { get; set; }
        public int LoadTimeMin { get; set; }
        public int UnloadTimeMin { get; set; }
        public string StChange { get; set; }
        public string Dest { get; set; }
        public string ToStation { get; set; }
    }
    public class ReportDAC
    {
        private static readonly string ConnectionStringKey = "SUPSDB";
        private static string connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
        public static SqlConnection con = new SqlConnection(connectionString);
        public IList<ReportRow> AllReport()
        {
            SqlCommand command = new SqlCommand("AllReport", con);
            command.Connection = con;//устанавливаем соединение
            command.CommandType = CommandType.StoredProcedure;
            con.Open();
            SqlDataReader data = command.ExecuteReader();
            IList<ReportRow> rows = new List<ReportRow>();
            while (data.Read())
            {
                rows.Add( new ReportRow()
                {
                    CarNum = data.GetString(0),
                    TravelTimeMin = data.GetInt32(1),
                    LoadTimeMin = data.GetInt32(3),
                    UnloadTimeMin = data.GetInt32(2),
                    StChange = data.GetString(4),
                    Dest = data.GetString(5),
                    ToStation = data.GetString(6)

                }
                );
                
                
            }
            con.Close();
            return rows;
        }
    }
    public class CargoDAC
    {
        private static readonly string ConnectionStringKey = "SUPSDB";
        private static string connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
        //public static SqlConnection con = new SqlConnection(connectionString);
        public void Update(CargoRows row)
        {
            using (var con = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand("CargoUpdate", con))
                {
                
                command.CommandType = CommandType.StoredProcedure;

                if (row.Name == null) row.Name = "<Не определено>";
                if (row.Description == null) row.Description = "<Не определено>";
                if (row.Mnemocode == null) row.Mnemocode = "<Не определено>";
                if (row.Code == null) row.Code = "<Не определено>";
                //command.CommandText = "UPDATE CARGO set   Name=(@Name) ,Description=(@Description) ,Mnemocode=(@Mnemocode),Code=(@Code) ,IsEmpty=(@IsEmpty) ,IsUsed=(@IsUsed) where Cargo_ID=(@ID)";
                command.Parameters.AddWithValue("@Name", row.Name);
                command.Parameters.AddWithValue("@Description", row.Description);
                command.Parameters.AddWithValue("@Mnemocode", row.Mnemocode);
                command.Parameters.AddWithValue("@IsEmpty", row.IsEmpty);
                command.Parameters.AddWithValue("@IsUsed", row.IsUsed);
                command.Parameters.AddWithValue("@ID", row.ID);
                command.Parameters.AddWithValue("@Code", row.Code);


                con.Open();

                command.ExecuteNonQuery();
                }
            }
        }
        public void Update1(string Name, string Description, string Code, string Mnemocode, bool IsUsed, bool IsEmpty, Object Data,int ID)
        {
            if (Data == null)
            {
                this.Update(new CargoRows { Name = Name, Description = Description, Code = Code, Mnemocode = Mnemocode, IsUsed = IsUsed, IsEmpty = IsEmpty, ID = ID });

            }
            else
            {
                using (var con = new SqlConnection(connectionString))
                {
                    using (var command = new SqlCommand("UpdateWithPic", con))
                    {

                        command.CommandType = CommandType.StoredProcedure;
                        //command.CommandText = "UPDATE CARGO set   Name=(@Name) ,Description=(@Description) ,Mnemocode=(@Mnemocode),Code=(@Code) ,IsEmpty=(@IsEmpty) ,IsUsed=(@IsUsed) where Cargo_ID=(@ID)";
                        command.Parameters.AddWithValue("@Name", Name);
                        command.Parameters.AddWithValue("@Description", Description);
                        command.Parameters.AddWithValue("@Mnemocode", Mnemocode);
                        command.Parameters.AddWithValue("@IsEmpty", IsEmpty);
                        command.Parameters.AddWithValue("@IsUsed", IsUsed);
                        command.Parameters.AddWithValue("@ID", ID);
                        command.Parameters.AddWithValue("@Code", Code);
                        command.Parameters.AddWithValue("@Data", Data);

                        con.Open();

                        command.ExecuteNonQuery();
                        
                    }
                }
            }
        }
        public void Delete(Int32 ID)
        {
           
            
            
           /* command.CommandText = "Delete from tbDislocation where CargoCodeTariff_ShortCode=(select ShortCode from CARGO where Cargo_ID=(@ID));Delete from CARGO where Cargo_ID=(@ID)";
                
            
            command.Parameters.AddWithValue("@ID", row.ID);
            
            */

            using (var con = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand("CargoDelete", con))
                {

                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@ID", ID);

                    command.ExecuteNonQuery();
                    
                }
            }
        }
        public void Insert1( string Name, string Description, string Code ,string Mnemocode, bool IsUsed, bool IsEmpty,Object Data)
        {
            using (var con = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand("InsertWithPic", con))
                {

                    command.CommandType = CommandType.StoredProcedure;
                    //command.CommandText = "INSERT into Cargo(Name,Description,Mnemocode,Code,ShortCode,IsEmpty,IsUsed) values ((@Name) ,(@Description) ,(@Mnemocode),(@Code),(@ShortCode) ,(@IsEmpty) ,(@IsUsed))";



                    command.Parameters.AddWithValue("@Name", Name);
                    command.Parameters.AddWithValue("@Description", Description);
                    command.Parameters.AddWithValue("@Mnemocode", Mnemocode);
                    command.Parameters.AddWithValue("@IsEmpty", IsEmpty);
                    command.Parameters.AddWithValue("@IsUsed", IsUsed);
                    command.Parameters.AddWithValue("@Code", Code);
                    if (Code == null) command.Parameters.AddWithValue("@ShortCode", 0);
                    else
                        command.Parameters.AddWithValue("@ShortCode", Code.Substring(0, Code.Length - 1));
                    command.Parameters.AddWithValue("@Data", Data);


                    con.Open();

                    command.ExecuteNonQuery();
                }
            }
        }
        public void Insert(CargoRows row)
        {
            using (var con = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand("CargoInsert", con))
                {

                    command.CommandType = CommandType.StoredProcedure;
                    //command.CommandText = "INSERT into Cargo(Name,Description,Mnemocode,Code,ShortCode,IsEmpty,IsUsed) values ((@Name) ,(@Description) ,(@Mnemocode),(@Code),(@ShortCode) ,(@IsEmpty) ,(@IsUsed))";
                    if (row.Name == null) row.Name = "<Не определено>";
                    if (row.Description == null) row.Description = "<Не определено>";
                    if (row.Mnemocode == null) row.Mnemocode = "<Не определено>";
                    if (row.Code == null) row.Code = "<Не определено>";


                    command.Parameters.AddWithValue("@Name", row.Name);
                    command.Parameters.AddWithValue("@Description", row.Description);
                    command.Parameters.AddWithValue("@Mnemocode", row.Mnemocode);
                    command.Parameters.AddWithValue("@IsEmpty", row.IsEmpty);
                    command.Parameters.AddWithValue("@IsUsed", row.IsUsed);
                    command.Parameters.AddWithValue("@Code", row.Code);
                    command.Parameters.AddWithValue("@ShortCode", row.Code.Substring(0, row.Code.Length - 1));


                    con.Open();

                    command.ExecuteNonQuery();
                }
            }
        }
        public CargoRows SelectRow(int ID)
        {

            using (var con = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand("SelectRowCargo", con))
                {
                    command.Connection = con;//устанавливаем соединение

                    //command.CommandText = "select  Cargo.Cargo_ID as ID,Cargo.Name as Name,ISNULL(Cargo.Description,0) as Description,Cargo.Code as Code,Cargo.Mnemocode as Mnemocode,Cargo.IsEmpty as IsEmpty,Cargo.IsUsed as IsUsed"
                    //                    + " from Cargo where Cargo_ID=(@ID)";

                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@ID", ID);
                    CargoRows row;


                    con.Open();
                    SqlDataReader data = command.ExecuteReader();
                    data.Read();

                    if (data.HasRows)
                    {
                        row = new CargoRows()
                        {
                            ID = data.GetInt32(0),
                            //Name = data.GetString(1),
                            // Description = data.GetString(2),
                            // Code = data.GetString(3),
                            //Mnemocode = data.GetString(4),
                            IsUsed = data.GetBoolean(5),
                            IsEmpty = data.GetBoolean(6)


                        };

                        if (data.IsDBNull(1)) row.Name = "<Не определено>"; else row.Name = data.GetString(1);
                        if (data.IsDBNull(4)) row.Mnemocode = "<Не определено>"; else row.Mnemocode = data.GetString(4);
                        if (data.IsDBNull(2)) row.Description = "<Не определено>"; else row.Description = data.GetString(2);
                        if (data.IsDBNull(3)) row.Code = "<Не определено>"; else row.Code = data.GetString(3);
                    }


                    else
                    {
                        row = new CargoRows();


                    }
                    con.Close();
                    return row;
                }
            }

        }
        public IList<CargoRows> SelectPage(int MaxRow, int StartRow)
        {
            using (var con = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand("SelectPageCargo", con))
                {

                    command.CommandType = CommandType.StoredProcedure;
                    // command.CommandText = "select top 20 Cargo.Cargo_ID as ID,Cargo.name as Name,ISNULL(Cargo.Description,0) as Description,Cargo.Code as Code,Cargo.Mnemocode as Mnemocode,Cargo.IsEmpty as IsEmpty,Cargo.IsUsed as IsUsed"
                    //                    + " from Cargo"; 


                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@MaxRow", MaxRow);
                    if (StartRow == 0) StartRow = 1;
                    command.Parameters.AddWithValue("@StartRow", StartRow);
                    con.Open();
                    SqlDataReader data = command.ExecuteReader();
                    IList<CargoRows> rows = new List<CargoRows>();
                    while (data.Read())
                    {
                        CargoRows row = new CargoRows()
                        {

                            ID = data.GetInt32(1),
                            //Name = data.GetString(2),
                            // Description = data.GetString(4),
                            //Code = data.GetString(6),

                            //Mnemocode = data.GetString(9),
                            IsUsed = data.GetBoolean(11),
                            IsEmpty = data.GetBoolean(12)
                        };
                        if (data.IsDBNull(2)) row.Name = "<Не определено>"; else row.Name = data.GetString(2);
                        if (data.IsDBNull(9)) row.Mnemocode = "<Не определено>"; else row.Mnemocode = data.GetString(9);
                        if (data.IsDBNull(4)) row.Description = "<Не определено>"; else row.Description = data.GetString(4);
                        if (data.IsDBNull(6)) row.Code = "<Не определено>"; else row.Code = data.GetString(6);

                        rows.Add(row);

                    }

                    return rows;
                }
            }
        }
        public static int SelectCount()
        {
            using (var con = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand("SelectCountCargo", con))
                {

                    command.CommandType = CommandType.StoredProcedure;
                    con.Open();
                    SqlDataReader data = command.ExecuteReader();
                    data.Read();

                    int i = data.GetInt32(0);
                    
                    return i;
                }
            }
        }
        public IList<CargoRows> SelectAllRows()
        {



            using (var con = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand("SelectAllRowsCargo", con))
                {

                    command.CommandType = CommandType.StoredProcedure;
                    // command.CommandText = "select top 20 Cargo.Cargo_ID as ID,Cargo.name as Name,ISNULL(Cargo.Description,0) as Description,Cargo.Code as Code,Cargo.Mnemocode as Mnemocode,Cargo.IsEmpty as IsEmpty,Cargo.IsUsed as IsUsed"
                    //                    + " from Cargo"; 




                    con.Open();
                    SqlDataReader data = command.ExecuteReader();
                    IList<CargoRows> rows = new List<CargoRows>();
                    while (data.Read())
                    {

                        rows.Add(new CargoRows()
                        {
                            ID = data.GetInt32(0),
                            Name = data.GetString(1),
                            Description = data.GetString(2),
                            Code = data.GetString(3),

                            Mnemocode = data.GetString(4),
                            IsUsed = data.GetBoolean(5),
                            IsEmpty = data.GetBoolean(6)
                        }
                        );


                    }


                    return rows;
                }
            }

        }
        public byte[] LoadImg(int id)
        {



            using (var con = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand("LoadImg", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ID", id);
                    con.Open();
                    byte[] pict = null;
                    SqlDataReader data = cmd.ExecuteReader();
                    data.Read();

                    if (!data.IsDBNull(0)) pict = (byte[])data.GetValue(0);
                    con.Close();
                    return pict;
                }
            }
        }
         
    }
    public class ObjectDataSourceDAC
    {
            private static readonly string ConnectionStringKey = "SUPSDB";
            private string connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
            public IList<DislocRow> SelectAllRows()
            {


                using (var con = new SqlConnection(connectionString))
                {
                    using (var command = new SqlCommand("SelectAllRowsDislocation", con))
                    {

                        command.CommandType = CommandType.StoredProcedure;
                        /* command.CommandText = "select InventoryCarriageNumber,WeightTons,DestStation.Name as DestStation,Cargo.Name as CargoName,ConsigneeCode,ParkType,StartStation.Name as StartStation,CarOperation.Name as CarOperation,SUBSTRING(OperationDate,1,2)+'.'+SUBSTRING(OperationDate,3,2)+'.'+OperationYear as Date,SUBSTRING(OperationTime,1,2)+':'+SUBSTRING(OperationTime,3,2) as Time,OpStation.Name as OpStation,DelRoad.Name as DelRoad,RecRoad.Name as RecRoad,Dis.TrainIndex as TrainIndex,Dis.TrainNumber as TrainNumber"
                                + " from tbDislocation as Dis,Cargo,Station as DestStation,Station as StartStation,CarOperation,Station as OpStation,Road as DelRoad,Road as RecRoad"
                               +  " where Cargo.ShortCode=Dis.CargoCodeTariff_ShortCode and"
                               + " DestStation.Code = Dis.DestinationStation_Code and"
                               + " StartStation.Code=Dis.StartStation_Code and"
                               + " CarOperation.Code=Dis.OperationCode_Code and CarOperation.OperationType=Dis.OperationType_Code and"
                               + " OpStation.Code=Dis.StationOfOperation_Code and"
                              +  " Dis.DeliveryRoad_Code=DelRoad.Code and"
                              +  " Dis.ReceiptRoad_Code=RecRoad.Code;";//текст команды*/
                        con.Open();
                        SqlDataReader data = command.ExecuteReader();
                        IList<DislocRow> rows = new List<DislocRow>();
                        int i = 20;
                        while (data.Read() && i-- > 0)
                        {
                            rows.Add(new DislocRow()
                            {
                                InventoryCarriageNumber = data.GetString(0),
                                WeightTons = data.GetString(1),
                                DestStation = data.GetString(2),
                                CargoName = data.GetString(3),
                                ConsigneeCode = data.GetString(4),
                                ParkType = data.GetString(5),
                                StartStation = data.GetString(6),
                                CarOperation = data.GetString(7),
                                Date = data.GetString(8),
                                Time = data.GetString(9),
                                OpStation = data.GetString(10),
                                DelRoad = data.GetString(11),
                                RecRoad = data.GetString(12),
                                TrainIndex = data.GetString(13),
                                TrainNumber = data.GetString(14)
                            }
                            );
                        }
                        return rows;
                    }
                }

            }
        
    }
}
